import json

from flask import jsonify
import sqlite3


# SQLite数据库文件
DATABASE = '/opt/myapp/db/users.db'


# 创建数据库连接
def get_db():
    conn = sqlite3.connect(DATABASE)
    return conn

# 注册用户接口
def register(username, account, password):
    username = str(username)
    account = str(account)
    password = str(password)

    if not username or not account or not password:
        return json.dumps({"error": "Missing required fields", "code": 404})

    conn = get_db()
    cursor = conn.cursor()
    try:
        cursor.execute('INSERT INTO users (username, account, password) VALUES (?, ?, ?)',
                       (username, account, password))
        conn.commit()
        conn.close()
        return json.dumps({"message": "User registered successfully", "code": 0})
    except sqlite3.IntegrityError:
        conn.close()
        return json.dumps({"error": "Account already exists", "code": 400})

# 登录用户接口
def login(account,password):
    account = str(account)
    password = str(password)

    if not account or not password:
        return json.dumps({"error": "Missing required fields", "code": 400})

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users WHERE account = ? AND password = ?', (account, password))
    user = cursor.fetchone()
    conn.close()
    name = get_username(account)
    if user:
        return json.dumps({"message": "Login successful", "User": name, "code": 0})
    else:

        return json.dumps({"error": "Invalid credentials", "code": 400})


def get_users():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    users = cursor.fetchall()
    conn.close()

    users_list = [{"id": user[0], "username": user[1], "account": user[2]} for user in users]
    return jsonify(users_list)


def get_username(account):
    account = str(account)
    # 连接到数据库并执行查询
    conn = get_db()
    user = conn.execute('SELECT username FROM users WHERE account = ?', (account,)).fetchone()
    conn.close()

    if user:
        return user[0]
    else:
        return 'Account not found'

def delete_user(account):
    account = str(account)

    if not account:
        return jsonify({'error': 'Missing account field'}), 400

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM users WHERE account = ?', (account,))
    conn.commit()
    conn.close()

    return jsonify({'message': f'User with account {account} deleted successfully'})

